# -*- coding: utf-8 -*-

# test_CriarBD
# demostra um teste na criacao
# de base de dados
# Elisio Goncalves Ribeiro

import sqlite3


global bd_con 

def BDCompleto(endereco, listaDados):
    bdcursor = ligacaoABD(endereco)
    
    criarTabelaTipos_Estabelecimento(bdcursor, listaDados)
    
    criarTabelaNomes_Estabelecimento(bdcursor, listaDados)
    
    criarTabelaRegimes_Tempo(bdcursor, listaDados)

    criarTabelaUltimos_Graus(bdcursor, listaDados)
    
    criarTabelaProfessores(bdcursor, listaDados)

    criarTabelaCursos(bdcursor, listaDados)

    criarTabelaCategorias(bdcursor, listaDados)
    
    criarTabelaFicha_Curso(bdcursor, listaDados)
    
    criarTabelaFicha_Docentes(bdcursor, listaDados)
pass


def fecharLigacaoBD(bdcursor):
    bd_con.commit()
    bdcursor.close()
    
pass

def ligacaoABD(endereco):
    ''' recebe um endereco e faz a ligacao ao respectivo ficheiro
    ou cria um novo no endereco caso nao exista
     - retorn um bdcursor ligado a base de dados criada/ligada'''
    global bd_con
    try:
        # conexao/criacao no disco da Base de Dados
        bd_con = sqlite3.connect(endereco)
        bd_con.text_factory = str
    except sqlite3.Error:
        raise
    else:
    # cricao do cursor para execucao dos comandos sobre a BD
        bdcursor = bd_con.cursor()
        return bdcursor
pass
    
def criarTabelaTipos_Estabelecimento(bdcursor, listaDados):
    bdcursor.execute('CREATE TABLE IF NOT EXISTS \
            Tipo_Estabelecimento (designacao VARCHAR(50) PRIMARY KEY)')
    
    # comando de preenchimento
    sqlCmd = "INSERT INTO Tipo_Estabelecimento (designacao) VALUES (?)"
    # preenchimento
    bdcursor.executemany(sqlCmd, listaDados)

pass
    
def criarTabelaNomes_Estabelecimento(bdcursor, listaDados):
    '''Recebe uma lista com dos dados a preencher na 
    respectiva tabela em cada linha da lista vem o conjunto dos campos'''
    bdcursor.execute('CREATE TABLE IF NOT EXISTS \
            Nome_Estabelecimento (designacao VARCHAR(50) PRIMARY KEY)')

    # comando de preenchimento
    sqlCmd = "INSERT INTO Nome_Estabelecimento (designacao) VALUES (?)"
    # preenchimento
    bdcursor.executemany(sqlCmd, listaDados)

pass
    
def criarTabelaRegimes_Tempo(bdcursor, listaDados):
    '''Recebe uma lista com dos dados a preencher na 
    respectiva tabela em cada linha da lista vem o conjunto dos campos'''
    bdcursor.execute('CREATE TABLE IF NOT EXISTS \
            Regimes_Tempo (designacao VARCHAR(50) PRIMARY KEY)')
    
    # comando de preenchimento
    sqlCmd = "INSERT INTO Regimes_Tempo (designacao) VALUES (?)"
    # preenchimento
    bdcursor.executemany(sqlCmd, listaDados)

pass

def criarTabelaUltimos_Graus(bdcursor, listaDados):
    '''Recebe uma lista com dos dados a preencher na 
    respectiva tabela em cada linha da lista vem o conjunto dos campos'''
    bdcursor.execute('CREATE TABLE IF NOT EXISTS \
            Ultimos_Graus (nome_graus VARCHAR(50) PRIMARY KEY)')
            
    # comando de preenchimento
    sqlCmd = "INSERT INTO Ultimos_Graus (nome_graus) VALUES (?)"
    # preenchimento
##    for vlr in  xrange(len(listaDados)):
##       # linha = (linha[0],linha(1),linha[2].strip(),linha[3].strip(),linha[4],linha[5])
##        print listaDados[vlr]
##        bdcursor.execute(sqlCmd, listaDados[vlr])
##    pass
    bdcursor.executemany(sqlCmd, listaDados)

pass
    
def criarTabelaProfessores(bdcursor, listaDados):
    '''Recebe uma lista com dos dados a preencher na 
    respectiva tabela em cada linha da lista vem o conjunto dos campos'''
    bdcursor.execute('CREATE TABLE IF NOT EXISTS \
            Professores (cod_docente INTEGER PRIMARY KEY, nome_completo VARCHAR(50))')
            
    # comando de preenchimento
    sqlCmd = "INSERT INTO Professores (cod_docente, nome_completo) VALUES (?,?)"
    # preenchimento
##    listaDados = __getDataToUtf(listaDados)
    bdcursor.executemany(sqlCmd, listaDados)

pass

def criarTabelaCursos(bdcursor, listaDados):
    '''Recebe uma lista com dos dados a preencher na 
    respectiva tabela em cada linha da lista vem o conjunto dos campos'''
    bdcursor.execute('CREATE TABLE IF NOT EXISTS \
            Cursos (designacao VARCHAR(50) PRIMARY KEY)')
            
    # comando de preenchimento
    sqlCmd = "INSERT INTO Cursos (designacao) VALUES (?)"
    # preenchimento
    bdcursor.executemany(sqlCmd, listaDados)

pass

def criarTabelaCategorias(bdcursor, listaDados):
    '''Recebe uma lista com dos dados a preencher na 
    respectiva tabela em cada linha da lista vem o conjunto dos campos'''
    bdcursor.execute('CREATE TABLE IF NOT EXISTS \
            Categorias (nome VARCHAR(50) PRIMARY KEY)')
            
    # comando de preenchimento
    sqlCmd = "INSERT INTO Categorias (nome) VALUES (?)"
    # preenchimento
    bdcursor.executemany(sqlCmd, listaDados)

pass
    
def criarTabelaFicha_Curso(bdcursor, lst):
    '''Recebe uma lista dos dados a preencher na tabela
    cada linha tem um tuplo com os valores dos campos
    lst[ano] - tuplo de valores para cada campo'''
    #criacao da tabela com os dados do docentes na BD
    bdcursor.execute('CREATE TABLE IF NOT EXISTS Ficha_curso (id INTEGER PRIMARY KEY AUTOINCREMENT,\
                         nomeProfessor REFERENCES Professores (cod_docente), \
                        grau REFERENCES Ultimos_Graus (nome_graus),\
                         curso_espec REFERENCES Cursos (designacao), \
                        ano INTEGER(10))')
                        
    # comando de preenchimento
    sqlCmd = "INSERT INTO Ficha_curso (nomeProfessor, grau, curso_espec, \
                        ano) VALUES (?,?,?,?)"
    # preenchimento
    for vlr in  lst.keys():
        bdcursor.executemany(sqlCmd, lst[vlr])
    pass
pass

#criacao e preenchimento da tabela dos professores/docentes
def criarTabelaFicha_Docentes(bdcursor, lst):
    '''Recebe um dict cujo as chaves sao para valor ano e os valores do
    dict sao para os restantes campos
    dict[ano] - retorna valores a introduzir na tabela'''
    #criacao da tabela com os dados do docentes na BD
    bdcursor.execute('CREATE TABLE IF NOT EXISTS Pessoal_Docente (id INTEGER PRIMARY KEY AUTOINCREMENT,\
                        nomeProfessor REFERENCES Professores (cod_docente), \
                        grau REFERENCES Ultimos_Graus (nome_graus),\
                        curso_espec REFERENCES Cursos (designacao), \
                        estabelec REFERENCES Nome_Estabelecimento (designacao), \
                        categoria REFERENCES Categorias (nome), \
                        tipo_estabelec REFERENCES Tipo_Estabelecimento(designacao), \
                        ano INTEGER(10))')
        
    # comando de preenchimento
    sqlCmd = "INSERT INTO Pessoal_Docente (nomeProfessor, grau,\
                        curso_espec, estabelec, categoria, tipo_estabelec, \
                        ano) VALUES (?,?,?,?,?,?,?)"
    # preenchimento
    for vlr in  lst.keys():
        bdcursor.executemany(sqlCmd, lst[vlr])
    pass
pass

# obter as listas do ficheiro a escrever na base de dados
def AoCriarLigarBD(csvDados, ondeGuardar):

    rg = []     #lista regime
    te = []     #tio estabelecimento
    ne = []     #nome estabelecimento
    ug = []     #graus academicos
    cd = []     #docentes
    ct = []     #categoria
    fd = {}     #ficha docente
    fc = {}     #ficha curso
    curso = []
   
    for dt in csvDados:     #iterar sobre o Dictreader do ficheiro csv
        
        if((dt['Curso'],) not in curso):        #obter a lista de cursos
            curso.append((dt['Curso'],))
        pass
        #
        if(dt['Ano Lectivo'] not in fd):        #lista de ficha de docentes e ficha de cursos
            fd[dt['Ano Lectivo']] =[(dt['Nome'],dt['Último Grau'], dt['Curso'],
                                    dt['Nome do Estabelecimento'],dt['Categoria'],
                                    dt['Tipo de Estabelecimento'], dt['Ano Lectivo'])]
            
            fc[dt['Ano Lectivo']] =[(dt['Nome'],dt['Último Grau'], dt['Curso'],
                                    dt['Ano Lectivo'])]
        else:
            fd[dt['Ano Lectivo']].append((dt['Nome'],dt['Último Grau'], dt['Curso'],
                                    dt['Nome do Estabelecimento'],dt['Categoria'],
                                    dt['Tipo de Estabelecimento'], dt['Ano Lectivo']))
            
            fc[dt['Ano Lectivo']].append((dt['Nome'],dt['Último Grau'], dt['Curso'],
                                    dt['Ano Lectivo']))
        pass
        
        if((dt['Tipo de Estabelecimento'],) not in te):     #lista de tipo de estabelecimento
            te.append((dt['Tipo de Estabelecimento'],))
        pass
        
        if((dt['Nome do Estabelecimento'],) not in ne):     #lista de nomes de  ""
            ne.append((dt['Nome do Estabelecimento'],))
        pass
        
        if((dt['Regime de Tempo'],) not in rg):             #lista de regimes de tempo
            rg.append((dt['Regime de Tempo'],))
        pass
        
        if((dt['Categoria'],) not in ct):                   #lista de categorias
            ct.append((dt['Categoria'],))  
        pass 
        
        prof=(int(dt['Código do Docente']),dt['Nome'])
        if(prof not in cd):                                 #lista de professores e seus codigos
            cd.append(prof)
            
        if((dt['Último Grau'],) not in ug):                 #lista de graus academicos
            ug.append((dt['Último Grau'],))            
        pass
    pass
    
    try:
        #ligar ao endereco passado
        bdc = ligacaoABD(ondeGuardar)    
        #a seguir com o cursor bdc cria-se as tabelas e preenche-se
        criarTabelaProfessores(bdc, cd)
        
        criarTabelaCategorias(bdc, ct)
        
        criarTabelaRegimes_Tempo(bdc, rg)
        
        criarTabelaNomes_Estabelecimento(bdc, ne)
        
        criarTabelaTipos_Estabelecimento(bdc, te)
        
        criarTabelaCursos(bdc, curso)
        
        criarTabelaUltimos_Graus(bdc, ug)
        
        criarTabelaFicha_Docentes(bdc, fd)
        
        criarTabelaFicha_Curso(bdc, fc)
    except sqlite3.Error:
        raise
    else:
        fecharLigacaoBD(bdc)
    pass
pass

def ObterValAnos(endereco):
    SqlCmd = "SELECT DISTINCT ano FROM Pessoal_Docente"
        
    try:
        #ligar ao endereco passado
        bdc = ligacaoABD(endereco)        
    except sqlite3.Error:
        raise
    else:
        try:            
            bdc.execute(SqlCmd)
        except sqlite3.Error:
            raise
        else:
            resultado = bdc.fetchall()
            fecharLigacaoBD(bdc)
            return resultado
        pass
    pass
pass

def AoFazerConsultaBD(param):
    SqlCmd1 = """SELECT DISTINCT nomeProfessor, ano FROM Pessoal_Docente
    WHERE ano = ?"""
    SqlCmd3 = """SELECT nomeProfessor, ano FROM Pessoal_Docente
    WHERE categoria = ?"""
    SqlCmd2 = """SELECT nomeProfessor, estabelec, ano FROM Pessoal_Docente
    WHERE estabelec = ?"""
    SqlCmd0 = """SELECT DISTINCT estabelec, ano FROM Pessoal_Docente
    WHERE ano = ? order by ano"""
    SqlCmd = "SELECT DISTINCT * FROM Pessoal_Docente"
    
    try:
        bdc = ligacaoABD(param['bd'])
        bdc.execute(SqlCmd1,(param['ano'],))
        
        if (param['lista'] == 0):               #lista de estabelecimento por ano
            bdc.execute(SqlCmd0, (param['ano'],))
        elif (param['lista'] == 1):         #professores por ano
            bdc.execute(SqlCmd1, (int(param['ano']),))
            
        elif (param['lista'] == 2):         #professores por estabelecimento
            bdc.execute(SqlCmd2, (param['estab'],))
        elif (param['lista'] == 3):         #professores por categoria
            bdc.execute(SqlCmd3, (param['categoria'],))
    except sqlite3.Error:
        raise
    else:
##        bdc.execute(SqlCmd,(param['ano'],))        
        listaRes = bdc.fetchall()
        return listaRes
    pass
pass
